package com.whg.demo.biz;

import jxl.Sheet;
import jxl.Workbook;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import java.io.*;
import java.nio.file.Files;
import java.util.UUID;

/**
 * @Author whg
 * @create 2023/6/16 15:06
 * @description：
 */
public class TestExcel2 {

    public static void main(String[] args) throws IOException{
        //读取excel模板
        //单据模板路径
        String filePath = "D:\\home\\project_two\\excel\\单据模板.xlsm";
        //单据模板文件
        File file = null;
        try{
            file = new File(filePath);
        }catch (Exception e){
            System.out.println("单据模板不存在");
            e.printStackTrace();
            return;
        }
        Workbook workbook = null;
        try{
            workbook = Workbook.getWorkbook(file);
        }catch (Exception e){
            System.out.println("读取单据模板出错：" + e.getMessage());
            e.printStackTrace();
            return;
        }
        //获取待填充数据sheet页
        Sheet[] sheets = workbook.getSheets();
        if (sheets.length < 2){
            System.out.println("单据模板缺少必备的sheet页");
            return;
        }
        //sheet1：出口货物明细单
        Sheet sheet1 = sheets[0];
        //sheet2：出口商品明细
        Sheet sheet2 = sheets[1];
        //创建待导出excel文件
        File temp = null;
        InputStream is = null;
        WritableWorkbook wWorkbook = null;
        try{
            //创建临时文件
            temp = Files.createTempFile(UUID.randomUUID().toString(),
                    file.getName().substring(file.getName().lastIndexOf("."))).toFile();
            wWorkbook = Workbook.createWorkbook(temp);
            //处理出口货物明细单sheet页
            WritableSheet wSheet1 = wWorkbook.createSheet(sheet1.getName(), 0);

            //处理出口商品明细sheet页
            WritableSheet wSheet2 = wWorkbook.createSheet(sheet2.getName(), 1);
            //复制其余sheet页
            for (int i = 2; i < sheets.length; i++){
                wWorkbook.importSheet(sheets[i].getName(),i,sheets[i]);
            }
            //写入数据
            wWorkbook.write();
            //关闭工作簿
            wWorkbook.close();
            //文件转base64
            is = new FileInputStream(temp);
            //String base64Data = TlUtils.base64Encode(TlUtils.readStreamByte2(is));
        }catch (Exception e){
            System.out.println("导出单据文件出错：" + e.getMessage());
            e.printStackTrace();
        }finally {
            if (null != wWorkbook){
                try {
                    wWorkbook.close();
                } catch (Exception e) {
                    System.out.println("生成单据文件出错：" + e.getMessage());
                    e.printStackTrace();
                    return;
                }
            }
            if (null != is){
                is.close();
            }
            if (null != file){
                file.delete();
            }
        }
    }
}
